ШАГ 7 - Групповые функции

Цель этих упражнений -- дать вам практические навыки использования групповых функций для отбора групп данных. Обращайте внимание на используемые псевдонимы.
Для работы с групповыми функциями синтаксис команды SELECT, определенный в простейшем варианте в третьем шаге, придется расширить добавив фразы GROUP BY и HAVING.

SELECT [DISTINCT {*|столбец [псевдоним], ..... }
FROM {таблица, ....... }
WHERE условие(я)
GROUP BY столбец1, [столбец2, .....]
HAVING критерии_отбора_групп_по_групповым_характеристикам
ORDER BY {столбец|выражение, .... } [ASC|DESC]

Фраза GROUP BY разделяет результаты на подгруппы. Описанные ниже групповые функции вычисляют агрегированные значения по подгруппам. Естественно GROUP BY оперирует только со строками, отобранными по критериям фразы WHERE.
Фраза HAVING отбирает часть групп, часто используя для этого групповые функции. Вообще HAVING может записываться и до и после фразы GROUP BY, но рекомендуется естественный порядок, приведенный выше.

Список групповых функций

AVG ([DISTINCT|ALL] n)     -- возвращает среднее значение в столбце не учитывая пустых значений;

COUNT ([DISTINCT|ALL] выражение)     -- количество строк в группе, в которых выражение имеет непустое значение;

MAX ([DISTINCT|ALL] выражение)
MIN ([DISTINCT|ALL] выражение)     -- максимльное (минимальное) значения выражения в группе;

STDDEV ([DISTINCT|ALL] n)     -- математическое ожидание в группе;

SUM ([DISTINCT|ALL] n)     -- сумма значений игнорируя пустые значения;

VARIANCE ([DISTINCT|ALL] n)     -- дисперсия в группе;

Квалификатор DISTINCT заставляет групповую функцию оперировать только с неповторяющимися значениями.
Квалификатор ALL, наоборот, заставляет использовать все значения.
Для всех групповых функций тип данных выражения может быть CHAR, NUMBER или DATE.
Все групповые функции кроме COUNT(*) игнорируют пустые значения.
Предложение WHERE не может использоваться для задания ограничений на групповые функции.

Правило: во фразе SELECT при использовании групповых функций можно записывать только те столбцы, по которым ведется разбиение на группы. Иначе говоря, любой столбец, стоящий в SELECT не в групповой функции должен стоять и в предложении GROUP BY. Кроме таких столбцов можно записывать литералы и псевдостолбцы.

Упражнения

1. Найдем все отделы, где работает больше трех сотрудников.

SELЕСТ DEPTNO, COUNT(*) FRОМ ЕМР GROUP BY DЕPTNO HAVING COUNT(* ) > 3;

В окне SQL*Plus Вы должны получить следующие значения:

 DEPTNO  COUNT(*)
 ------ ---------
     20         5
     30         6

2. Перечислим самых низкооплачиваемых сотрудников у каждого руководителя. Исключим все группы, где минимальная зарплата меньше 1000. Рассортируем выходные данные по зарплате.

SELЕСТ МGR, MIN(SAL) FRОМ ЕМР GROUP BY MGR HAVING MIN(SAL) >= 1000 ORDER BY MIN(SAL);

В окне SQL*Plus Вы должны получить следующие значения:

   MGR   MIN(SAL)
 ----- ----------
  7788       1100
  7782       1300
  7839       2450
  7566       3000
             5000

AVG ([DISTINCT\|ALL] n)		-- возвращает среднее значение в столбце не учитывая пустых значений;
COUNT ([DISTINCT\|ALL] выражение)		-- количество строк в группе, в которых выражение имеет непустое значение;
MAX ([DISTINCT\|ALL] выражение) MIN ([DISTINCT\|ALL] выражение)		-- максимльное (минимальное) значения выражения в группе;
STDDEV ([DISTINCT\|ALL] n)		-- математическое ожидание в группе;
SUM ([DISTINCT\|ALL] n)		-- сумма значений игнорируя пустые значения;
VARIANCE ([DISTINCT\|ALL] n)		-- дисперсия в группе;